﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class ChuyenKhauDAL
    {
        public static List<ChuyenKhauPublic> LoadChuyenKhauBySoCK(int SoChuyenKhau)
        {
            string chuoi = @"select [MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + " and NoiCap in(select MaPX from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            return ck.ToList();
        }
        public static List<ChuyenKhauPublic> LoadChuyenDenBySoCK(int SoChuyenKhau)
        {
            string chuoi = @"select [MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + " and NoiCap not in(select MaPX from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            return ck.ToList();
        }
        public static List<ChuyenKhauPublic> LoadChuyenKhauByMaNK(int MaNK, string TenServer, string TenData)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(@"select [MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from [" + TenServer + "].[" + TenData + "].[dbo].[ChuyenKhau] where MaNK=" + MaNK);
            return ck.ToList();
        }
        public static int CountChuyenDiByYear(int firstyear, int lastyear)
        {
            var cd = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear);
            return cd.FirstOrDefault();
        }
        public static int CountChuyenDenByYear(int firstyear, int lastyear)
        {
            var cden = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) not in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear);
            return cden.FirstOrDefault();
        }

        public static int LoadMaCK()
        {
            var ck = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaCK),0) as MaCK from ChuyenKhau");
            return ck.FirstOrDefault();
        }
       
        
        public static int LoadSoChuyenKhau()
        {
            var ck = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(SoChuyenKhau),0) from ChuyenKhau 
                                                        where LEFT(RIGHT('0'+cast(SoChuyenKhau as varchar(8)),8),2) in (select right(MaPX,2) as MaPX from ThietLap)");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenDinByYear(int firstyear, int lastyear)
        {
            var cden = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear+"and Len(NoiChuyen)=6");
            return cden.FirstOrDefault();
        }
//        public static bool TestChuyenDinByMaNK()
//        {
//            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(@"
//                                                                select * from ChuyenKhau where LEFT(right('0'+MaCK,8),2)=(select MaPX from ThietLap) and LEFT(right('0'+NoiChuyen,2),2)!=(select MaPX from ThietLap)  and MaNK=1000001");
//        }
        public static void UpdateChuyenDen(int SoChuyenKhau, byte MaQH, string ThuongTru, string LyDoChuyen, DateTime NgayCap, string NguoiCap, byte ChucVu, int NoiCap, string NguoiNhan, byte ChucVuNguoiNhan, int MaCK)
        {
            string chuoi = "update ChuyenKhau set SoChuyenKhau=" + SoChuyenKhau +"',MaQH=" + MaQH + ",ThuongTru='" + ThuongTru + "',LyDoChuyen='" + LyDoChuyen + ",NgayCap='" + NgayCap + "',NguoiCap='" + NguoiCap + "',ChucVuNguoiCap='" + ChucVu + "',NoiCap=" + NoiCap + ",NguoiNhan='" + NguoiNhan + "',ChucVuNguoiNhan=" + ChucVuNguoiNhan + " where MaCK=" + MaCK;
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            Common.db.SaveChanges();
        }
        public static void DeleteChuyenKhau(int MaCK)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>("delete  from ChuyenKhau where MaCK=" + MaCK);
            Common.db.SaveChanges();
        }
        public static void UpdateChuyenDi(byte MaQH, string ChuHo, int NoiChuyen, string LyDoChuyen, string NguoiCap, int ChucVu, int MaCK)
        {
            string chuoi = "update ChuyenKhau set MaQH=" + MaQH + ",ChuHo='" + ChuHo + "',NoiChuyen=" + NoiChuyen + ",LyDoChuyen='" + LyDoChuyen + "',NguoiCap='" + NguoiCap + "',ChucVuNguoiCap=" + ChucVu + " where MaCK=" + MaCK;
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            Common.db.SaveChanges();
        }
        //public static List<ChuyenKhauPublic> LoadChuyenKhauBySoCK(int SoChuyenKhau)
        //{
        //    string chuoi = @"select [MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + " and left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in(select right(MaPX,2) from ThietLap)";
        //    var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
        //    return ck.ToList();
        //}
    }
}
